Create Staging Tables in Staging Database and Populate the Staging Tables - Continued3 11

To Load Load_StgEnds





Source VW_Ends:



Columns Tab:




Aggregate Function:




Derived Columns:



 

Derived Columns Text:



GETDATE()

GETDATE()

ISNULL([End_Is this a W2 employee?]) ? "Unknown" : [End_Is this a W2 employee?]

ISNULL([End_If not a W2 employee, please provide the Subvendor Name]) ? "Unknown" : [End_If not a W2 employee, please provide the Subvendor Name]

ISNULL([End_Converted to FTE?]) || TRIM([End_Converted to FTE?]) == "" ? "Unknown" : [End_Converted to FTE?]

 

OLE DB Command




Mappings Tab:



To Load Load_StgRenewals:





Source Renewals:

SQL Command:




SQL Command Text:


WITH CTE AS (

select  ROW_NUMBER() over (Partition by [Work Order ID]

order by [Work Order Start Date] DESC  ) as StartDateRankorder, *

from [dbo].[Renewals]

where  [Work Order Start Date] is not null

)



SELECT [Work Order ID], [Revision Reason], [Revision #], [Renewal?], [On Time?],[Work Order Start Date], [Work Order End Date], 

[Previous Work Order Start Date], [Previous Work Order End Date], [First Approval Received Date],  [Work Order Create Date], 

[Work Order Approved Date], [Remove?]  FROM CTE

WHERE StartDateRankorder = 1



Columns Tab:




Data Conversion:




Derived Column:



 

Derived Column Text:

 

GETDATE()

GETDATE()

ISNULL([Revision Reason]) ? "Unknown" : [Revision Reason]

ISNULL([Work Order ID]) ? "Unknown" : [Work Order ID]

ISNULL([Revision #]) ? 0 : [Revision #]

ISNULL([Renewal?]) ? "Unknown" : [Renewal?]

ISNULL([On Time?]) ? "Unknown" : [On Time?]

ISNULL([Remove?]) ? "Unknown" : [Remove?]

ISNULL([Copy of Work Order Start Date]) ? "Unknown" : [Copy of Work Order Start Date]

ISNULL([Copy of Work Order End Date]) ? "Unknown" : [Copy of Work Order End Date]

ISNULL([Copy of Previous Work Order End Date]) ? "Unknown" : [Copy of Previous Work Order End Date]

ISNULL([Copy of Previous Work Order Start Date]) ? "Unknown" : [Copy of Previous Work Order Start Date]

ISNULL([Copy of First Approval Received Date]) ? "Unknown" : [Copy of First Approval Received Date]

ISNULL([Copy of Work Order Create Date]) ? "Unknown" : [Copy of Work Order Create Date]

ISNULL([Copy of Work Order Approved Date]) ? "Unknown" : [Copy of Work Order Approved Date]





Conditional Split:


 

Conditional Split Text:

LEN([Remove?]) < 1


Derived Columns 1:


Union All:




OLE DB Destination:



Mappings Tab:



Conditional Split transformation

https://www.sqlshack.com/ssis-conditional-split-transform-overview/